package com.eorchis.module.trainneedscope.dao.impl;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.dao.IDaoSupport.QueryStringType;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.department.domain.DepartmentUser;
import com.eorchis.module.trainneedscope.dao.ITrainNeedScopeDao;
import com.eorchis.module.trainneedscope.domain.QueryClassUserBean;
import com.eorchis.module.trainneedscope.domain.TrainNeedScope;
import com.eorchis.module.trainneedscope.domain.TrainNeedScopeQueryBean;
import com.eorchis.module.trainneedscope.ui.commond.TrainNeedScopeQueryCommond;
import com.eorchis.module.trainneedscope.ui.commond.TrainNeedScopeValidCommond;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: TrainNeedScopeDaoImpl<br>
 * Description: 培训需求调查对象数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Max
 * @createDate 2014-08-27
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.trainneedscope.dao.impl.TrainNeedScopeDaoImpl")
public class TrainNeedScopeDaoImpl
					extends HibernateAbstractBaseDao
					implements ITrainNeedScopeDao {
	
	public Class<? extends IBaseEntity> entityClass() {
		return TrainNeedScope.class;
	}
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		TrainNeedScopeQueryCommond trainNeedScopeQueryCommond = (TrainNeedScopeQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM TrainNeedScope t");
		defaultQueryConditionBuilder.addCondition("t.questionnaireSurveyObjectId", CompareType.IN, trainNeedScopeQueryCommond.getSearchQuestionnaireSurveyObjectIds());
		defaultQueryConditionBuilder.addCondition("t.questionnaireSurveyObjectId", CompareType.EQUAL,trainNeedScopeQueryCommond.getSearchQuestionnaireSurveyObjectId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
	}
	
	
	
	/**
	 * 添加人员或部门到问卷范围
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
	public boolean addEntityIntoTrainNeedScope(final TrainNeedScopeValidCommond vCommond)
			throws Exception {
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			public Boolean doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				try {
					if(vCommond.getAddEntityIds()!=null&&vCommond.getAddEntityIds().length>0) {
						 StringBuffer sql = new StringBuffer();
						 sql.append("insert into QUESTIONNAIRE_SURVEY_OBJECT ");
						 sql.append(" (QUESTIONNAIRE_SURVEY_OBJECT_ID,QUESTIONNAIRE_ID,SURVEY_OBJECT_TYPE,SURVEY_OBJECT_ID,CREATE_DATE,ACTIVE_STATE)");
						 sql.append(" values (?,?,?,?,?,?)");
						 PreparedStatement pst = conn.prepareStatement(sql.toString());
						 for(int i=0;i<vCommond.getAddEntityIds().length;i++){
							 pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
							 pst.setString(2, vCommond.getSearchQuestionNaireId());
							 pst.setInt(3, vCommond.getAddEntityType());//表示部门或者用户
							 pst.setString(4, vCommond.getAddEntityIds()[i]);//部门或者用户id
							 pst.setDate(5, new java.sql.Date(new Date().getTime()));
							 pst.setInt(6, TrainNeedScope.ACTIVE_STATE_Y);
							 pst.addBatch();
						 }
						 pst.executeBatch();
						 pst.close();
						 return true;
					 }else {
						 return true;
					 }
				}catch(Exception e) {
					e.printStackTrace();
					return false;
				}
			}
		});
	}
	
	
	/**
	 * 查询问卷下的人员列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
	public List<TrainNeedScopeQueryBean> getUserListByTrainNeedScope(TrainNeedScopeQueryCommond qCommond) throws Exception{
		
		StringBuffer shql=new StringBuffer();
		shql.append("select tns.questionnaireSurveyObjectId as linkId,tns.questionnaire.questionnaireId as questionnaireId,u.userId as userId,u.userName as userName, " +
				" u.loginID as loginID,dlc.dataName as dutyName,dept.deptName as deptName,scd.dataName as sexName" +
		        " from TrainNeedScope tns,DepartmentUser du inner join du.department dept " +
		        " left join du.user u left join u.dutyLevelCode dlc left join u.sexCode scd");
		//查询数量的sql
		StringBuffer countHql=new StringBuffer("from TrainNeedScope tns,DepartmentUser du inner join du.department dept left join du.user u left join u.dutyLevelCode dlc left join u.sexCode scd");
		//查询条件的sql
		StringBuffer hql=new StringBuffer();
		hql.append(" where 1=1");
		hql.append(" and tns.surveyObjectId=u.userId");
		
		Map<String, Object> values = new HashMap<String, Object>();
		if(qCommond.getSearchQuestionnaireId()!=null&&!"".equals(qCommond.getSearchQuestionnaireId())) {
			hql.append(" and tns.questionnaire.questionnaireId=:id");
			values.put("id", qCommond.getSearchQuestionnaireId());
		}
		if(qCommond.getSearchEntityType()!=null&&!"".equals(qCommond.getSearchEntityType())) {
			hql.append(" and tns.surveyObjectType=:entityType");
			values.put("entityType", TrainNeedScope.SURVER_TYPE_USER);
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(TrainNeedScopeQueryBean.class);
		
		countHql.append(hql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = qCommond.calculate(count.longValue());
		
		//执行查询
		// 数据列表查询
		shql.append(hql);
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			shql.append(" order by u.userId desc");
		}
		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<TrainNeedScopeQueryBean> list=new ArrayList<TrainNeedScopeQueryBean>();
		list=(List<TrainNeedScopeQueryBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		
		return list;
	}
	
	
	/**
	 * 查询问卷下部门列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
	public List<TrainNeedScopeQueryBean> getDeptListByTrainNeedScope(TrainNeedScopeQueryCommond qCommond) throws Exception{
		StringBuffer shql=new StringBuffer();
		
		shql.append("select qso.QUESTIONNAIRE_SURVEY_OBJECT_ID as linkId,qso.SURVEY_OBJECT_ID as questionnaireId,");
		shql.append(" dt.DEPNAME as deptName,");
		shql.append(" qso.DUTY_CODES as dutyCodes, ");
		shql.append(" (select b.depName from base_department b where b.DEPID=dt.parent_id) as upDeptName ");//查询上级部门的名称
//		shql.append(" (select count(u.userid) from base_department_user bu,base_department bt,base_user u " +
//				" where bt.DEPID=bu.DEPID and bu.USERID=u.USERID and bt.TREEPATH like CONCAT(dt.TREEPATH,'%')) as bigUserCount");//查询这个部门中有多少人包括子部门
		
		shql.append(" from QUESTIONNAIRE_SURVEY_OBJECT qso,base_department dt");
		
		//查询数量的sql
		StringBuffer countHql=new StringBuffer("select count(*) from QUESTIONNAIRE_SURVEY_OBJECT qso,base_department dt");
		//查询条件的sql
		StringBuffer hql=new StringBuffer();
		hql.append(" where 1=1");
		hql.append(" and qso.SURVEY_OBJECT_ID=dt.DEPID");
		
		Map<String, Object> values = new HashMap<String, Object>();
		if(qCommond.getSearchQuestionnaireId()!=null&&!"".equals(qCommond.getSearchQuestionnaireId())) {
			hql.append(" and qso.QUESTIONNAIRE_ID=:id");
			values.put("id", qCommond.getSearchQuestionnaireId());
		}
		if(qCommond.getSearchEntityType()!=null&&!"".equals(qCommond.getSearchEntityType())) {
			hql.append(" and qso.SURVEY_OBJECT_TYPE=:entityType");
			values.put("entityType", qCommond.getSearchEntityType());
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(TrainNeedScopeQueryBean.class);
		
		countHql.append(hql);
		//查询总数量
		List<BigInteger> executeFind=(List<BigInteger>) this.executeFind(QueryStringType.SQL, countHql.toString(), values);
		Long count=Long.valueOf(executeFind.get(0).toString());
		//查询总数量
		//计算分页
		int firstResult = qCommond.calculate(count.longValue());
		int maxResult=qCommond.getLimit();
		//计算分页
		
		//执行查询
		// 数据列表查询
		shql.append(hql);
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			//shql.append(" order by otsl.IS_HAVE ");
		}
		
		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		queryCondition1.setQueryStringType(QueryStringType.SQL);
		List<TrainNeedScopeQueryBean> list=(List<TrainNeedScopeQueryBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		
		return list;
	}
	
	/**
	 * 查询未选人员列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
	public List<QueryClassUserBean> findNoSelectedUserList(TrainNeedScopeQueryCommond qCommond)
			throws Exception {
		
		StringBuffer shql=new StringBuffer();
		shql.append(" select u.userName as userName,u.userId as userId,u.loginID as loginId,");
		shql.append(" dlc.dataName as dutyName,scd.dataName as sexName,d.deptName as deptName");
		shql.append(" from  DepartmentUser dtu inner join dtu.user u inner join dtu.department d ");
		shql.append(" left join u.sexCode scd left join u.dutyLevelCode dlc");
		
		StringBuffer countHql=new StringBuffer(" from DepartmentUser dtu inner join dtu.user u inner join dtu.department d left join u.sexCode scd left join u.dutyLevelCode dlc ");
		Map<String, Object> values = new HashMap<String, Object>();
		
		StringBuffer hql=new StringBuffer();
		hql.append(" where 1=1 and u.activeState=1");
		if(qCommond.getSearchYesOrNoSelectUser()!=null&&qCommond.getSearchYesOrNoSelectUser().equals("no")) {
			hql.append(" and u.userId not in( select tns.surveyObjectId from TrainNeedScope tns where tns.questionnaire.questionnaireId=:targetId and tns.surveyObjectType=:entityType)");
			values.put("targetId", qCommond.getSearchQuestionnaireId());
			values.put("entityType", TrainNeedScope.SURVER_TYPE_USER);//查询类型为用户
		}
		
		if(qCommond.getSearchTreePath()!=null&&!"".equals(qCommond.getSearchTreePath())) {
			hql.append(" and d.treepath like :treePath");
			values.put("treePath", "%"+qCommond.getSearchTreePath().trim()+"%");
		}
		
		if(qCommond.getSearchUserName()!=null&&!"".equals(qCommond.getSearchUserName())) {
			hql.append(" and u.userName like :userName");
			values.put("userName", "%"+qCommond.getSearchUserName().trim()+"%");
		}
		
		if(qCommond.getSearchLoginId()!=null&&!"".equals(qCommond.getSearchLoginId())) {
			hql.append(" and u.loginID =:loginID");
			values.put("loginID",qCommond.getSearchLoginId());
		}
		if(qCommond.getSearchDeptName()!=null&&!"".equals(qCommond.getSearchDeptName())) {
			hql.append(" and d.deptName like :deptName");
			values.put("deptName", "%"+qCommond.getSearchDeptName().trim()+"%");
		}
		
		if(qCommond.getSearchDuty()!=null&&!"".equals(qCommond.getSearchDuty())) {
			
			hql.append(" and dlc.dataCode =:dataCode");
			values.put("dataCode",qCommond.getSearchDuty());
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(QueryClassUserBean.class);
		
		countHql.append(hql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = qCommond.calculate(count.longValue());
		
		//执行查询
		// 数据列表查询
		shql.append(hql);
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			shql.append(" order by u.userId desc");
		}
		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<QueryClassUserBean> list=(List<QueryClassUserBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		
		return list;
	}
	
	/**
	 * 查询未选部门列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
	public List<QueryClassUserBean> findNoSelectedDeptList(TrainNeedScopeQueryCommond qCommond)
			throws Exception {
		
		Department d =new Department();
		DepartmentUser du=new DepartmentUser();
		//du.getd
		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer shql=new StringBuffer();
		shql.append(" select d.depid as depId,d.DEPNAME as deptName,");
		shql.append("(select count(u.userid) from base_department_user du,base_department bt,base_user u" +
				" where bt.DEPID=du.DEPID and du.USERID=u.USERID" +
				" and bt.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid=d.depid),'%')) as lUserCount,");//查询部门下的人员数据包括子部门
		//shql.append("(select count(*) from DepartmentUser du where du.department.deptID=d.deptID and du.department.treepath like:treepath) as bigUserCount,");//查询部门下的人员数据包括子部门
		//values.put("treepath", "%"+qCommond.getSearchTreePath()+"%");
		shql.append("(select dt.DEPNAME from base_department dt where dt.depid=d.parent_id) as upDeptName");//查询上级单位名称
		shql.append(" from base_department d ");
		
		StringBuffer countHql=new StringBuffer(" from base_department d ");
		
		StringBuffer hql=new StringBuffer();
		hql.append(" where 1=1 and d.ACTIVE_STATE=1");
		if(qCommond.getSearchDepId()!=null&&!"".equals(qCommond.getSearchDepId())) {
		    if(qCommond.getSearchCurrPardId()!=null&&!"".equals(qCommond.getSearchCurrPardId())) {
				hql.append(" and d.depid=:parentID");
				values.put("parentID", qCommond.getSearchCurrPardId());
			}else {
				hql.append(" and d.parent_id=:parentID");
				values.put("parentID", qCommond.getSearchDepId());
			}
		}
		
		if(qCommond.getSearchYesOrNoSelectDept()!=null&&qCommond.getSearchYesOrNoSelectDept().equals("no")) {
			hql.append(" and d.depid not in( select tns.SURVEY_OBJECT_ID from questionnaire_survey_object tns where tns.QUESTIONNAIRE_ID=:targetId and tns.SURVEY_OBJECT_TYPE=:entityType)");
			values.put("targetId", qCommond.getSearchQuestionnaireId());
			values.put("entityType", TrainNeedScope.SURVER_TYPE_DEPT);//查询类型为用户
		}
		if(qCommond.getSearchDeptName()!=null&&!"".equals(qCommond.getSearchDeptName())) {
			hql.append(" and d.DEPNAME like :deptName");
			values.put("deptName", "%"+qCommond.getSearchDeptName().trim()+"%");
		}
		if(qCommond.getSearchUpDeptName()!=null&&!"".equals(qCommond.getSearchUpDeptName())) {
			hql.append(" and d.parent_id in(select dt.depid from base_department dt where dt.DEPNAME like :updeptName) ");
			values.put("updeptName", "%"+qCommond.getSearchUpDeptName().trim()+"%");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
				.setResultObject(QueryClassUserBean.class);
		
		
		countHql.append(hql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = qCommond.calculate(count.longValue());
		
		shql.append(hql);
		List<SortInfoBean> sortInfoList = qCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			shql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			shql.append(" order by d.ORDER_NUM ");
		}
		defaultQueryConditionBuilder.setBaseQueryString(shql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		queryCondition1.setQueryStringType(QueryStringType.SQL);
		List<QueryClassUserBean> list=(List<QueryClassUserBean>) this.findList(queryCondition1,
				firstResult, qCommond.getLimit());
		
		return list;
	}
	
	/**
	 * 删除问卷范围关联关系表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
	public void deleteTargetScopeLink(TrainNeedScopeValidCommond vCommond) throws Exception {
		String [] linkIds=vCommond.getSearchLinkIds();
		Map<String, Object> values = new HashMap<String, Object>();
		if(linkIds!=null&&linkIds.length>0) {
			String hql="delete from TrainNeedScope otsl where otsl.questionnaireSurveyObjectId in(:linkId)";
			values.put("linkId", linkIds);
			this.executeUpdate(QueryStringType.HQL, hql, values);
		}
	}
	
	/**
	 * 更改问卷范围内的职级范围
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-08-27
	 */
	@Override
   public void updateTargerScopeDuty(TrainNeedScopeValidCommond vCommond) throws Exception{
		Map<String, Object> values = new HashMap<String, Object>();
		String hql="update TrainNeedScope otsl set otsl.dutyCodes=:dutyCodes where otsl.questionnaireSurveyObjectId=:linkId";
		values.put("dutyCodes", vCommond.getSearchDutyCodes());
		values.put("linkId", vCommond.getSearchLinkId());
		this.executeUpdate(QueryStringType.HQL, hql, values);
   }

	@Override
	public Integer getUserNumByScopeId(String lindId,String [] dutyCodes) throws Exception{
		StringBuffer shql=new StringBuffer(); 
		
		Map<String, Object> values = new HashMap<String, Object>();
		shql.append(" select count(u.userid) from base_department_user bu,base_department bt,base_user u ,questionnaire_survey_object otsl" +
		" where bt.DEPID=bu.DEPID and bu.USERID=u.USERID  and bt.TREEPATH like CONCAT((select d.treepath from base_department d where d.depid=otsl.SURVEY_OBJECT_ID),'%')");
	    shql.append(" and otsl.QUESTIONNAIRE_SURVEY_OBJECT_ID='"+lindId+"' and otsl.SURVEY_OBJECT_TYPE='1' ");
	    if(dutyCodes!=null) {
	    	shql.append("and u.DUTY_LEVEL_CODE in(:dutyCodes)");
	    	values.put("dutyCodes", dutyCodes);
	    }
	   List<BigInteger> list= (List<BigInteger>) this.executeFind(QueryStringType.SQL, shql.toString(), values);
	   return Integer.parseInt(list.get(0).toString());
	}
}
